package cn.edu.sdjzu.xg.bysj.dao;
//201902104061徐中堂
import cn.edu.sdjzu.xg.bysj.domain.GraduateProjectType;
import util.JdbcHelper;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collection;

public final class GraduateProjectTypeDao {
    //新建实例对象
    private static GraduateProjectTypeDao graduateProjectTypeDao = new GraduateProjectTypeDao();
    //数据库链接对象
    private Connection connection = null;
    //语句盒子
    private Statement statement = null;
    //预编译语句盒子
    private PreparedStatement preparedStatement = null;
    //存储过程语句盒子
    private CallableStatement callableStatement = null;
    //结果集
    private ResultSet resultSet = null;
    //无参构造器
    private GraduateProjectTypeDao() {
    }
    //获得数据库的实例
    public static GraduateProjectTypeDao getInstance() {
        return graduateProjectTypeDao;
    }
    //显示所有数据
    public Collection<GraduateProjectType> findAll() throws SQLException {
        //graduateProjectTypes 集合用于储存查找到的数据
        Collection<GraduateProjectType> graduateProjectTypes = new ArrayList<>();
        //获得数据库连接对象
        connection = JdbcHelper.getConn();
        //创建语句盒子
        statement = connection.createStatement();
        //执行sql语句并获得结果集
        resultSet = statement.executeQuery("select * from GraduateProjectType");
        //读取数据
        while (resultSet.next()){
            //新建foundGraduateProjectType对象，用于储存查找到的每一行的信息
            GraduateProjectType foundGraduateProjectType =
                    new GraduateProjectType(resultSet.getInt("id"),
                            resultSet.getString("description"),
                            resultSet.getString("no"),
                            resultSet.getString("remarks"));
            //添加进集合中
            graduateProjectTypes.add(foundGraduateProjectType);
        }
        //关闭结果集，语句盒子和数据库连接
        JdbcHelper.close(resultSet,statement,connection);
        //返回查找到的数据集合
        return graduateProjectTypes;
    }
    //根据id查找信息
    public GraduateProjectType find(Connection connection,Integer id) throws SQLException{
        //foundGraduateProjectType 集合用于储存查找到的数据
        GraduateProjectType foundGraduateProjectType = null;
        //新建语句盒子
        statement = connection.createStatement();
        //执行sql语句并获得结果集
        resultSet = statement.executeQuery("select * from GraduateProjectType where id = " + id);
        //读取数据
        if (resultSet.next()) {
            foundGraduateProjectType = new GraduateProjectType(resultSet.getInt("id"),
                    resultSet.getString("description"),
                    resultSet.getString("no"),
                    resultSet.getString("remarks"));
        }
        //关闭结果集，语句盒子和数据库连接
        JdbcHelper.close(resultSet, statement);
        //返回查找到的数据对象
        return foundGraduateProjectType;
    }
    //更新数据
    public boolean update(GraduateProjectType graduateProjectType) throws SQLException{
        //获得数据库连接
        connection = JdbcHelper.getConn();
        //预编译sql语句
        preparedStatement = connection.prepareStatement("update GraduateProjectType set description=?,no=?,remarks=? where id=?");
        //为sql语句参数赋值
        preparedStatement.setInt(4,graduateProjectType.getId());
        preparedStatement.setString(1,graduateProjectType.getDescription());
        preparedStatement.setString(2,graduateProjectType.getNo());
        preparedStatement.setString(3,graduateProjectType.getRemarks());
        //执行sql语句并获得受影响行数的值
        int changLine = preparedStatement.executeUpdate();
        //关闭预编译语句盒子
        preparedStatement.close();
        //关闭数据库连接
        connection.close();
        //更改成功返回true反之返回false
        if (changLine != 0){
            return true;
        }else {
            return false;
        }
    }
    //添加数据
    public boolean add(GraduateProjectType graduateProjectType) throws SQLException{
        //获得数据库链接
        connection = JdbcHelper.getConn();
        //新建存储过程语句盒子，预编译sql语句
        callableStatement = connection.prepareCall("call sp_addGraduateProjectType(?,?,?,?)");
        //为sql语句参数赋值，并获得数据库输出的id值
        callableStatement.registerOutParameter(4,Types.BIGINT);
        callableStatement.setString(1,graduateProjectType.getDescription());
        callableStatement.setString(2,graduateProjectType.getNo());
        callableStatement.setString(3,graduateProjectType.getRemarks());
        //执行sql语句并获得受影响的行数的值
        int changeLine = callableStatement.executeUpdate();
        //获得数据库输出的id值
        int id = callableStatement.getInt(4);
        //打印数据库为新增的数据的id值
        System.out.println("新添加的School的id为" + id);
        //关闭储存过程语句盒子
        callableStatement.close();
        //关闭数据库连接
        connection.close();
        //若添加成功则返回true反之返回false
        if (changeLine != 0){
            return true;
        }else {
            return false;
        }
    }
    //根据id删除数据
    public boolean delete(Integer id) throws SQLException{
        //获得数据库连接
        connection = JdbcHelper.getConn();
        //预编译sql语句
        preparedStatement = connection.prepareStatement("delete from GraduateProjectType where id=?");
        //为sql语句参数赋值
        preparedStatement.setInt(1,id);
        //执行sql语句并获得受影响的行数的值
        int changeLine = preparedStatement.executeUpdate();
        //关闭数据库链接
        connection.close();
        //关闭预编译语句盒子
        preparedStatement.close();
        //如果删除成功返回true反之返回false
        if (changeLine != 0 ){
            return true;
        }else {
            return false;
        }
    }
    //删除数据
    public boolean delete(GraduateProjectType graduateProjectType) throws SQLException{
        //获得graduateProjectType的id值，根据id在数据库中删除
        return this.delete(graduateProjectType.getId());
    }
}
